USE [NANS21V]
GO

/****** Object:  StoredProcedure [dbo].[PlayerInfo]    Script Date: 05/01/2014 09:52:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PlayerInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PlayerInfo]
GO

CREATE PROCEDURE [dbo].[PlayerInfo]
	@TAIKAINO int,
	@KYOGISYANO int
AS
SELECT 
	K.KYOGISYANO, 
	K.SYOZOKUNO1, 
	K.SYOZOKUNO2, 
	TS.SYOZOKUMEI,
	(convert(varchar(15), K.NUMBER1) + convert(varchar(15), K.NUMBER2)) AS NUMBER,
	K.KYOGISYAMEI, 
	K.KYOGISYAKANA, 
	K.KYOGISYARYAKU, 
	dbo.Seibetu(K.SEIBETU) AS SEIBETU,
	K.GAKUNEN, 
	K.SEINEN, 
	((CASE WHEN 13 < LEFT(K.GAPPI,2) AND  LEFT(K.GAPPI,2) < 0 THEN LEFT(K.GAPPI,2)+'ŒŽ' ELSE '' END)+
	(CASE WHEN 32 < RIGHT(K.GAPPI,2) AND  RIGHT(K.GAPPI,2) < 0 THEN RIGHT(K.GAPPI,2)+'“ú' ELSE '' END)) AS GAPPI,
	K.KOJINSYOZOKUTIMEI, 
	K.RIKUREN 
FROM 
	TBL_KYOGISYA AS K, 
	TBL_SYOZOKU AS TS
WHERE 
	K.TAIKAINO = @TAIKAINO 
	AND K.TAIKAINO = TS.TAIKAINO
	AND K.KYOGISYANO = @KYOGISYANO
	AND  TS.SYOZOKUNO = K.SYOZOKUNO1